<?php

class Mdl_listingmapmodel extends CI_Model {

	function __construct() {
		parent::__construct();
	}

	function get_table() {
		$table = "Listings";
		return $table;
	}

	//we also can do
	//var $table = "dfasfs";
	//and inside each function, we will change $table by $this->table

	function getListing_With_Where_Limit() {
		$sql = "SELECT DISTINCT PAG_TABLEXXX.total, PAG_TABLEXXX.Listing_ID, PAG_TABLEXXX.PropertyFor, PAG_TABLEXXX.StatusFID,
							PAG_TABLEXXX.CountryFID,PAG_TABLEXXX.CityFID,PAG_TABLEXXX.LocaltionFID,PAG_TABLEXXX.SubAreaFID,
							PAG_TABLEXXX.Tenure,PAG_TABLEXXX. StreetNameFID, PAG_TABLEXXX.ProjectNameFID,PAG_TABLEXXX.PropertyTypeFID,
							PAG_TABLEXXX.FloorArea_SM,PAG_TABLEXXX.FloorArea_SF,PAG_TABLEXXX.LandArea_SM,PAG_TABLEXXX.LandArea_SF,
							PAG_TABLEXXX.AskingPriceLocal,PAG_TABLEXXX.AskingPriceUSD,PAG_TABLEXXX.PriceInRMBPerSQMLocal, PAG_TABLEXXX.PriceInRMBPerSQMUSD,
							PAG_TABLEXXX.PriceUnit,PAG_TABLEXXX.ListingType,PAG_TABLEXXX.NewLaunch,PAG_TABLEXXX.Longitude, PAG_TABLEXXX.Latitude,
							PAG_TABLEXXX.Bedroom,PAG_TABLEXXX.Bathroom,PAG_TABLEXXX.MemberFID,PAG_TABLEXXX.ListingDate,
							PAG_TABLEXXX.LanguageFID_1,PAG_TABLEXXX.City_1,PAG_TABLEXXX.Location_1,PAG_TABLEXXX.Subarea_1,
							PAG_TABLEXXX.ProjectName_1,PAG_TABLEXXX.StreetName_1,
							PAG_TABLEXXX.PropertyType_1,CAST(PAG_TABLEXXX.[Description_1] AS NVARCHAR(MAX)) AS [Description_1]
					FROM
						(
							SELECT count(1) over( partition by 1 ) as total,  ROW_NUMBER() OVER ( ORDER BY  Listing_ID DESC) as rownum, l.*
							
							FROM dbo.view_listings_search l WHERE (1 = 1) AND StatusFID = 1 AND l.CountryFID = 8) AS PAG_TABLEXXX 
					WHERE rownum > 0 AND rownum <= 999999999";
		$query = $this->db->query($sql);

		return $query;
	}
	
	function getProjectListing($options) {
		$tsql_callSP = '{call spProjectListing(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)}';

		$params = array(
			array($options['localorInt'], SQLSRV_PARAM_IN),
			array($options['market'], SQLSRV_PARAM_IN),
			array($options['country'], SQLSRV_PARAM_IN),
			array($options['list_type'], SQLSRV_PARAM_IN),
			array($options['propertyfor'], SQLSRV_PARAM_IN),
			array($options['propertytype'], SQLSRV_PARAM_IN),
			array($options['cities'], SQLSRV_PARAM_IN),
			array($options['locations'], SQLSRV_PARAM_IN),
			array($options['subarea'], SQLSRV_PARAM_IN),
			array($options['price_min'], SQLSRV_PARAM_IN),
			array($options['price_max'], SQLSRV_PARAM_IN),
			array($options['size_min'], SQLSRV_PARAM_IN),
			array($options['size_max'], SQLSRV_PARAM_IN),
			array($options['bedroom_min'], SQLSRV_PARAM_IN),
			array($options['bedroom_max'], SQLSRV_PARAM_IN),
			array($options['bathroom_min'], SQLSRV_PARAM_IN),
			array($options['bathroom_max'], SQLSRV_PARAM_IN),
			array($options['keyword'], SQLSRV_PARAM_IN),
			array($options['features'], SQLSRV_PARAM_IN),
			array($options['facilities'], SQLSRV_PARAM_IN),
			array($options['tenu'], SQLSRV_PARAM_IN),
			array($options['offset'], SQLSRV_PARAM_IN),
			array($options['limit'], SQLSRV_PARAM_IN),
			array($options['orderby'], SQLSRV_PARAM_IN),
			array($options['language'], SQLSRV_PARAM_IN),
			array($options['listing_id'], SQLSRV_PARAM_IN)
		);

		$results = $this->db->exec_query($tsql_callSP, $params);

		$data = array();
		if ($results) {
			while ($row = sqlsrv_fetch_array($results, SQLSRV_FETCH_ASSOC)) {
				$data[] = $row;
			}
		}

		return $data;
	}

	function getListingWithWhere($options) {
		$tsql_callSP = '{call sp_listings_search_by_map(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)}';

		$params = array(
		array($options['country'], SQLSRV_PARAM_IN),
		array($options['list_type'], SQLSRV_PARAM_IN),
		array($options['propertyfor'], SQLSRV_PARAM_IN),
		array($options['propertytype'], SQLSRV_PARAM_IN),
		array($options['cities'], SQLSRV_PARAM_IN),
		array($options['locations'], SQLSRV_PARAM_IN),
		array($options['subarea'], SQLSRV_PARAM_IN),
		array($options['price_min'], SQLSRV_PARAM_IN),
		array($options['price_max'], SQLSRV_PARAM_IN),
		array($options['size_min'], SQLSRV_PARAM_IN),
		array($options['size_max'], SQLSRV_PARAM_IN),
		array($options['bedrooms'], SQLSRV_PARAM_IN),
		array($options['bathrooms'], SQLSRV_PARAM_IN),
		array($options['keyword'], SQLSRV_PARAM_IN),
		array($options['features'], SQLSRV_PARAM_IN),
		array($options['facilities'], SQLSRV_PARAM_IN),
		array($options['offset'], SQLSRV_PARAM_IN),
		array($options['limit'], SQLSRV_PARAM_IN),
		array($options['orderby'], SQLSRV_PARAM_IN),
		array(1, SQLSRV_PARAM_IN),
		array($options['listing_id'], SQLSRV_PARAM_IN)
		);

		$results = $this->db->exec_query($tsql_callSP, $params);

		$data = array();
		if ($results) {
			while ($row = sqlsrv_fetch_array($results, SQLSRV_FETCH_ASSOC)) {
				$data[] = $row;
			}
		}

		return $data;
	}
	
	function getProductRelated($listing_id) {
		//get city id based on listing id
		$city_id = $this->getCitty($listing_id);
		if($city_id) {
			//get listing based on city id
			$sql = "select TOP 10 l.Listing_ID,l.Price_Min, l.PriceUnit,
						(SELECT TOP 1 ld.City FROM dbo.ListingDescriptions ld WHERE l.Listing_ID = ld.ListingFID) AS City,
						(SELECT TOP 1 ld.Location FROM dbo.ListingDescriptions ld WHERE l.Listing_ID = ld.ListingFID) AS Location,
						(SELECT TOP 1 ld.Subarea FROM dbo.ListingDescriptions ld WHERE l.Listing_ID = ld.ListingFID) AS Subarea,
						(SELECT TOP 1 ld.ProjectName FROM dbo.ListingDescriptions ld WHERE l.Listing_ID = ld.ListingFID) AS ProjectName
					from Listings l
					left join City c ON l.CityFID = c.City_ID
					where l.StatusFID = 1 and c.LanguageFID = ". $this->config->item('config_language_id') ." and l.CityFID = ". $city_id ." and l.Listing_ID <> ". $listing_id ."";
			
			$query = $this->db->query($sql);
	
			if ($query->num_rows() > 0) {
				return $query->result();
			} else {
				return false;
			}
		} else {
			return false;
		}
	}
	
	function getMostViewed() {
		//get listing based on city id
		$sql = "select TOP 10 l.Listing_ID,l.Price_Min, l.PriceUnit, l.Viewed,
					(SELECT TOP 1 ld.City FROM dbo.ListingDescriptions ld WHERE l.Listing_ID = ld.ListingFID) AS City,
					(SELECT TOP 1 ld.Location FROM dbo.ListingDescriptions ld WHERE l.Listing_ID = ld.ListingFID) AS Location,
					(SELECT TOP 1 ld.Subarea FROM dbo.ListingDescriptions ld WHERE l.Listing_ID = ld.ListingFID) AS Subarea,
					(SELECT TOP 1 ld.ProjectName FROM dbo.ListingDescriptions ld WHERE l.Listing_ID = ld.ListingFID) AS ProjectName
				from Listings l
				where l.StatusFID = 1 and l.Viewed > 0
				order by l.Viewed DESC";
		
		$query = $this->db->query($sql);

		if ($query->num_rows() > 0) {
			return $query->result();
		} else {
			return false;
		}
	}
	
	function updateViewed($listing_id) {
		$sql = "UPDATE Listings SET Viewed = (Viewed + 1) WHERE Listing_ID = " . (int)$listing_id . "";
		$this->db->query($sql);
	}

	function updateHit($listing_id) {
		$sql = "UPDATE Listings SET Hits = (Hits + 1) WHERE Listing_ID = " . (int)$listing_id . "";
		$this->db->query($sql);
	}
	
	
	function getCitty($listing_id) {
		$this->db->select('CityFID');
		$this->db->from('dbo.Listings l');

		$where = array(
        	'l.Listing_ID'	=> $listing_id,
        	'l.StatusFID' 	=> 1
		);

		$this->db->where($where);

		$query = $this->db->get();

		$arr = array();

		if ($query->num_rows() > 0) {
			$row = $query->row();
			$city_id = $row->CityFID;
			return $city_id;
		} else {
			return false;
		}
	}

	function get($order_by) {
		$table = $this->get_table();

		$this->db->order_by($order_by);
		$query = $this->db->get($table);

		return $query;
	}

	function get_with_limit($limit, $offset, $order_by) {
		$table = $this->get_table();
		$this->db->limit($limit, $offset);
		$this->db->order_by($order_by);
		$query = $this->db->query($table);

		return $query;
	}

	function get_where($id) {
		$table = $this->get_table();
		$this->db->where('id', $id);
		$query = $this->db->get($table);

		return $query;
	}

	function get_where_custom($col, $value) {
		$table = $this->get_table();
		$this->db->where($col, $value);
		$query = $this->db->get($table);

		return $query;
	}

	function _insert($data) {
		$table = $this->get_table();
		$this->db->insert($table, $data);
	}

	function _update($id, $data) {
		$table = $this->get_table();
		$this->db->where('id', $id);
		$this->db->update($table, $data);
	}

	function _delete($id) {
		$table = $this->get_table();
		$this->db->where('id', $id);
		$this->db->delete($table);
	}

	function count_where($column, $value) {
		$table = $this->get_table();
		$this->db->where($column, $value);
		$query = $this->db->get($table);
		$num_rows = $query->num_rows();
		return $num_rows;
	}

	function count_all() {
		$table = $this->get_table();
		$query = $this->db->get($table);
		$num_rows = $query->num_rows();

		return $num_rows;
	}

	function get_max() {
		$table = $this->get_table();
		$this->db->select_max('id');
		$query = $this->db->get($table);
		$row = $query->row();
		$id = $row->id;
		return $id;
	}

	function _custom_query($mysql_query) {
		$query = $this->db->query($mysql_query);
		return $query;
	}

	function get_media($id, $type, $noofitem = 1) {
		$this->db->select('Top '. $noofitem .' FileName, FileExtension');
		$this->db->from('ListingMedia');
		if($type == "Cover") {
			$where = array(
	        	'ListingFID' 	=> $id,
	        	'MediaType'	=> $this->db->escape_str($type)
			);
		} else {
			$where = array(
	        	'ListingFID' 	=> $id,
	        	'MediaType!='	=> 'floor plan'
	        	);
		}

		$this->db->where($where);
		$this->db->order_by('Media_ID ASC');

		$query = $this->db->get();

		$arr = array();

		if ($query->num_rows() > 0) {
			if($noofitem == 1) {
				$arr = $query->row();
			} else {
				foreach ($query->result() as $row) {
					array_push($arr, $row);
				}
			}
			 
			return $arr;
		} else {
			return false;
		}
	}

	function get_proDir_media($id, $type, $noofitem = 1) {
		$this->db->select('Top '. $noofitem .' Filename, FileExtension');
		$this->db->from('PrjDirMedia');
		$where = array(
        	'PrjDirectoryFID' 	=>	$id,
        	'MediaType'			=> $type
		);

		$this->db->where($where);
		$this->db->order_by('Media_ID ASC');

		$query = $this->db->get();

		$arr = array();

		if ($query->num_rows() > 0) {
			if($noofitem == 1) {
				$arr = $query->row();
			} else {
				foreach ($query->result() as $row) {
					array_push($arr, $row);
				}
			}
		}

		return $arr;
	}

	function getFeatures($pro_dir_id, $lang_id, $market) {
		$this->db->select('f.Features_ID,f.Features');
		$this->db->from('dbo.Features f');
		$this->db->join('dbo.ListingFeatures lf', 'lf.FeatureFID = f.Features_ID');
		$this->db->join('dbo.Listings l', 'l.Listing_ID = lf.ListingFID');

		$where = array(
        	'l.Listing_ID'	=> $pro_dir_id,
        	'f.LanguageFID' 		=> $lang_id,
        	'f.MarketFID'			=> $this->db->escape_str($market)
		);

		$this->db->where($where);

		$query = $this->db->get();

		$arr = array();

		if ($query->num_rows() > 0) {
			foreach ($query->result() as $row) {
				array_push($arr, $row);
			}
			return $arr;
		} else {
			return false;
		}
	}

	function getFacilities($pro_dir_id, $lang_id, $market) {
		$this->db->select('f.Facilities_ID,f.Facilities');
		$this->db->from('dbo.Facilities f');
		$this->db->join('dbo.ListingFacilities lf', 'lf.FacilityFID = f.Facilities_ID');
		$this->db->join('dbo.Listings l', 'l.Listing_ID = lf.ListingFID');

		$where = array(
        	'l.Listing_ID'	=> $pro_dir_id,
        	'f.LanguageFID' 		=> $lang_id,
        	'f.MarketFID'			=> $this->db->escape_str($market)
		);

		$this->db->where($where);

		$query = $this->db->get();

		$arr = array();

		if ($query->num_rows() > 0) {
			foreach ($query->result() as $row) {
				array_push($arr, $row);
			}
			return $arr;
		} else {
			return false;
		}
	}
}

?>
